package persistenciaDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import conexaoDAO.Conexao;
import modelo.Usuario;


public class UsuarioDAO {
    private static boolean status = false;
    private static Connection conn = Conexao.getConnection(); 

 public static int id(){
 int numero = 0;
 try{
 PreparedStatement ps = null;
 ResultSet rs = null;
 String sql = "select nextval('usuario_id_seq')";
 ps = conn.prepareStatement(sql);
 rs = ps.executeQuery();
 if(rs.next())
 numero=rs.getInt(1);
         }
 catch(SQLException e){
     e.getMessage();
 }
  catch(Exception e){
     e.getMessage();
 }
 finally{
 return numero;
 }
 }   
    public static boolean insereUsuario(Usuario usuario){
     try{
            
            String SQL = "INSERT INTO usuario (nome, usuario , senha ) VALUES (?,?,?)";
            PreparedStatement ps = conn.prepareStatement(SQL);

            ps.setString(1, usuario.getNome());
            ps.setString(2, usuario.getUsuario());
            ps.setString(3, usuario.getSenha());
            ps.executeUpdate();
            status = true;

         } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
        finally{
        	Conexao.close();
                return status;
        }
    }
    public static boolean deletaUsuario(Usuario usuario){
    try{
            
            PreparedStatement ps = null;
            String SQL = "DELETE FROM usuario WHERE usuario =? and senha = ?";
            
            
            ps = conn.prepareStatement(SQL);
            
            ps.setString(1, usuario.getUsuario());
            ps.setString(2, usuario.getSenha());
            
            ps.executeUpdate();
            status = true;
         } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
        finally{
        	Conexao.close();
                return status;
        }

    }
    public static boolean alteraUsuario(Usuario usuario){
    try{
            PreparedStatement ps = null;
            
            String SQL = "UPDATE usuario SET senha = ? WHERE id = ?";
            ps.setString(1, usuario.getSenha());
            ps.setInt(2, usuario.getId());
            
            ps = conn.prepareStatement(SQL);
            
            ps.setString(1, usuario.getSenha());
            ps.setInt(2, usuario.getId());
            ps.executeUpdate();
            status = true;
          
            

        }  
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
    finally{
    	Conexao.close();
        return status;
    }
    }

     
     
     public static Usuario consultaUsuario(String usu, String sen){
         Usuario us = null;
         try {
            PreparedStatement ps = null;
            String SQL = "SELECT id, nome, usuario, senha, FROM usuario WHERE usuario = ? and senha = ? ";
            ps = conn.prepareStatement(SQL);
            ps.setString(1, usu);
            ps.setString(2, sen);
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
            String nome = rs.getString("nome");
            String usuario = rs.getString("usuario");
            String senha = rs.getString("senha");
            int id = rs.getInt("id");
            String email = UsuarioDAO.consultaEmail(us);
            us = new Usuario(id, nome, usuario, senha, email);
            }
            rs.close();
            Conexao.close();
            
        } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
		return us;
    	}
    // --------------------------------------------------------------------------------
        public static boolean insereEmail(Usuario usuario){
     try{
            
            String SQL = "INSERT INTO email (email, id_usuario ) VALUES (?,?)";
            PreparedStatement ps = conn.prepareStatement(SQL);

            ps.setString(1, usuario.getEmail());
            ps.setInt(2, usuario.getId());
 
            ps.executeUpdate();
            status = true;
         } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
        finally{
        	Conexao.close();
                return status;
        }
    }
    public static boolean deletaEmail(Usuario usuario){
    try{
            PreparedStatement ps = null;
            
            String SQL = "DELETE FROM email WHERE id_usuario =?";
            
            
            ps = conn.prepareStatement(SQL);
            
            ps.setInt(1, usuario.getId());            
            ps.executeUpdate();
            status = true;
         } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
        finally{
        	Conexao.close();
                return status;
        }

    }
    public static boolean alteraEmail(Usuario usuario){
    try{
            PreparedStatement ps = null;
            
            String SQL = "UPDATE email SET email = ? WHERE id_usuario = ?";
            ps = conn.prepareStatement(SQL);
            ps.setString(1, usuario.getEmail());
            ps.setInt(2, usuario.getId()); 
            ps.executeUpdate();
            status = true;
            

        }  
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
    finally{
    	Conexao.close();
        return status;
    }
    }

     
     
     public static String consultaEmail(Usuario usuario){
         String email = null;
         try {
            PreparedStatement ps = null;
            String SQL = "SELECT email WHERE id_usuario = ?";
            ps = conn.prepareStatement(SQL);
            ps.setInt(1, usuario.getId());
            ResultSet rs = ps.executeQuery();
            if (rs.next()){
            email =(rs.getString("email"));
            }
            rs.close();
            Conexao.close();
            
        } 
        catch(SQLException ex){
            System.out.println("falha sql exeption "+ ex.getMessage());
        }catch(Exception e){
           System.out.println("falha exeption " +e.getMessage());        
        } 
		return email;
    	}
}
 